package cn.andev.app.morsesignal;

import java.io.FileNotFoundException;
import java.util.HashMap;

import android.content.ContentValues;
import android.content.Context;  
import android.database.Cursor;  
import android.database.sqlite.SQLiteDatabase;  
import android.database.sqlite.SQLiteException;
import android.util.Log;

public class SQLiteContext {
	
	private static final String TAG = "SQLiteContext";	 
	private static final String KEY_TITLE = "TITLE";
	private static final String KEY_MSG = "MSG";
	private static final String KEY_KEY = "KEY";
	private static final String KEY_VALUE = "VALUE";
    //private static final String DATABASE_NAME = "morsesignal.db";
	//private static final String TABLE_NAME = "t_msg";
    public SQLiteDatabase db;    
    private Context _context;  // env context
    private String _dbName;
    private String _dbPath;
    private String _msgTblName;
    private String _configTblName;
  
    // open or create db
    SQLiteContext(Context context, String dbName, String msgTblName, String configTblName) {  
        _context = context;
        _dbName = dbName;
        _msgTblName = msgTblName;
        _configTblName = configTblName;
        db = _context.openOrCreateDatabase(dbName, Context.MODE_PRIVATE, null);  
        CreateTable();
        _dbPath = db.getPath();
        Log.i(TAG, "db path = " + db.getPath());
    }
    
    // open already existed db
    public boolean openDB(){
    	if(!db.isOpen()){
    		try { 
    	        db = SQLiteDatabase.openDatabase(_dbPath, null, SQLiteDatabase.OPEN_READWRITE);
    	        Log.i(TAG, "Open DB " + _dbPath + " ok");
    	        return true;
    	    } catch (SQLiteException  e) {
    	    	Log.e(TAG, "Open DB " + _dbPath + " error");
    	    	return false;
    	    }    		
    	}
    	Log.i(TAG, "DB " + _dbName + " is already opened");
    	return true;
    }
  
    /**
     * SQLite 3  
     *  TEXT
        NUMERIC
        INTEGER
        REAL
        NONE
     */
    public void CreateTable() {
    	// create msg table
        try {
            db.execSQL("CREATE TABLE " + _msgTblName + "("
                    + "_ID INTEGER PRIMARY KEY autoincrement,"
                    + KEY_TITLE + " TEXT,"
                    + KEY_MSG + " TEXT"
                    + ");");
            Log.i(TAG, "Create Table " + _msgTblName + " ok");
        } catch (Exception e) {
            Log.e(TAG, "Create Table " + _msgTblName + " err, table exists.");
        }
        
        // create config table
        try {
            db.execSQL("CREATE TABLE " + _configTblName + "("
                    + "_ID INTEGER PRIMARY KEY autoincrement,"
                    + KEY_KEY + " TEXT,"
                    + KEY_VALUE + " TEXT"
                    + ");");
            Log.i(TAG, "Create Table " + _configTblName + " ok");
        } catch (Exception e) {
            Log.e(TAG, "Create Table " + _configTblName + " err, table exists.");
        }
    }
    
    /** 
     * save custom msg
     * @param id default null
     * @param title
     * @param msg
     * @return 
     */
    public boolean saveMsg(String title, String msg){
        String sql = "";
        try{
            sql="insert into " + _msgTblName 
            	+ " values(null,'" + title + "','"+ msg + "')";
            db.execSQL(sql);
            Log.i(TAG,"insert Table " + _msgTblName +" ok, sql: " + sql);
            return true;
        }catch(Exception e){
            Log.e(TAG,"insert Table " + _msgTblName + "err, sql: " + sql);
            return false;
        }
    }
    
    /** 
     * save config
     * @param id default null
     * @param key
     * @param value
     * @return 
     */
    public boolean saveConfig(String key, String value){
        String sql = "";
        try{
            sql="insert into " + _configTblName 
            	+ " values(null,'" + key + "','"+ value + "')";
            db.execSQL(sql);
            Log.i(TAG,"insert to table " + _configTblName +" ok, sql: " + sql);
            return true;
        }catch(Exception e){
            Log.e(TAG,"insert to table " + _configTblName + "err, sql: " + sql);
            return false;
        }
    }
    
    public boolean deleteMsg(String title){
        try{
            db.delete(_msgTblName, KEY_TITLE+"="+title, null);
            Log.i(TAG,"delete item of table " + _msgTblName +" ok, title: " + title);
            return true;
        }catch(Exception e){
            Log.e(TAG,"delete item of table " + _msgTblName + "err, title: " + title);
            return false;
        }
    }
    
    public boolean deleteConfig(String key){
        try{
            db.delete(_configTblName, KEY_KEY+"="+key, null);
            Log.i(TAG,"delete item of table " + _configTblName +" ok, key: " + key);
            return true;
        }catch(Exception e){
            Log.e(TAG,"delete item of table " + _configTblName + "err, title: " + key);
            return false;
        }
    }
    
    public boolean updateMsg(long rowId, String title, String msg) {
        ContentValues args = new ContentValues();
        args.put(KEY_TITLE, title);
        args.put(KEY_MSG, msg);
        return db.update(_msgTblName, args, "_ID=" + rowId, null) > 0;
    }

    public boolean updateMsg(String title, String msg) {
        ContentValues args = new ContentValues();
        args.put(KEY_TITLE, title);
        args.put(KEY_MSG, msg);
        return db.update(_msgTblName, args, KEY_TITLE+"="+title, null) > 0;
    }
    
    public boolean updateConfig(long rowId, String key, String value) {
        ContentValues args = new ContentValues();
        args.put(KEY_KEY, key);
        args.put(KEY_VALUE, value);
        return db.update(_configTblName, args, "_ID=" + rowId, null) > 0;
    }

    public boolean updateConfig(String key, String value) {
        ContentValues args = new ContentValues();
        args.put(KEY_KEY, key);
        args.put(KEY_VALUE, value);
        return db.update(_configTblName, args, KEY_KEY+"="+key, null) > 0;
    }
    
    /**
     * Query all msg
     *
     * @return Cursor Pointer to result set, like ResultSet in JDBC
     */
    public Cursor loadAllMsgs(){
        Cursor cur = db.query(_msgTblName, new String[]{"_ID","TITLE","MSG"}, 
        						null, null, null, null, null);
        return cur;
    }
    
    public String readMsg(String title){
    	Cursor cur = db.query(_msgTblName, new String[]{"TITLE","MSG"}, 
				null, null, null, null, null);
    	while(cur.moveToNext()){
			if(cur.getString(0) == title)  
            	return cur.getString(1);
    	}
    	return null;
    }
    
    /**
     * Query all config
     *
     * @return Cursor Pointer to result set, like ResultSet in JDBC
     */
    public Cursor loadAllConfigs(){
        Cursor cur = db.query(_configTblName, new String[]{"_ID","KEY","VALUE"}, 
        						null, null, null, null, null);
        return cur;
    }
    
    public String readConfig(String key){
    	Cursor cur = db.query(_configTblName, new String[]{"KEY","VALUE"}, 
				null, null, null, null, null);
    	while(cur.moveToNext()){
			if(cur.getString(0) == key)  
            	return cur.getString(1);
    	}
    	return null;
    }
    
    public void close(){
        db.close();
    }
    
    public boolean isOpen(){
    	return db.isOpen();
    }
}
